﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Common;
using System.Data;

using System.Text;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
 
using CommonsLib.Utility;
using CommonsLib.Settings;

namespace CommonsLib.DB
{
    public class DBAccess:IDisposable
    {

        public const int LogStatus_Add=0;
        public const int LogStatus_Update=1;
        public const int LogStatus_Delete=2;
    
        private ConnectionInfo _server = null;
        private DbConnection _conn = null;
        private DbTransaction  _Trans = null;
         
        private DBAccess()
        {
        }
        public DBAccess(ConnectionInfo server)
        {
            _server = server;
        }

        public DbConnection Connection
        {
            get
            {
                if (_conn == null)
                {
                    _conn = CreateDbConnection(_server.Type, _server.ConnectionString);
                }

                return _conn;
            }
        }
         
        private   DbConnection CreateDbConnection(int serverType, string strConn)
        {
            DbConnection retConnection = null;
            if (serverType== ConnectionInfo.ServerType_Sql )
            {
                retConnection= SqlFactory.CreateDbConnection( strConn);
            }
            else
            {
                retConnection= MySqlFactory.CreateDbConnection( strConn);
            }
            
           
            return retConnection;
        }

         

        private DbCommand CreateCommand(int serverType)
        {
            if (serverType== ConnectionInfo.ServerType_Sql )
            {
                return SqlFactory.CreateCommand(this.Connection);
            }
            else
            {
                return MySqlFactory.CreateCommand( this.Connection);
            }
        }

        public KeyValuePair<string, IDataParameter[]> CreateParamSql(string strFieldName, string strTableName, object[] dr, int logStatus, string strKeyFieldName, object objKeyFieldValue)
        {
            if (_server.Type ==ConnectionInfo.ServerType_Sql)
            {
                return SqlFactory.CreateParamSql(strFieldName, strTableName, dr, logStatus, strKeyFieldName, objKeyFieldValue);
            }
            else
            {
                return MySqlFactory.CreateParamSql(strFieldName, strTableName, dr, logStatus, strKeyFieldName, objKeyFieldValue);
            }
        }

        public  DbDataReader GetDataReader(   string strSql)
        {
            return GetDataReader(strSql,null);
        }
        public DbDataReader GetDataReader(string strSql, IDataParameter[] arrParam)
        {
            return GetDataReader(strSql, arrParam, false);
        }
        public  DbDataReader GetDataReader(   string strSql, IDataParameter[] arrParam,bool blnFirstRow)
        {
            LogManager.Debug(string.Format("[{0}] {1}", _server.ToString(), strSql));
            if (arrParam != null)
            {
                LogManager.Debug(GetParamLog(arrParam));
            }

            if (Connection.State == ConnectionState.Closed)
            {

                Connection.Open();
            }
            DbCommand myCmd = CreateCommand(_server.Type);
                myCmd.CommandTimeout = 60;
                if (_blnInTrans)
                {
                    myCmd.Transaction = _Trans;
                }
                myCmd.CommandText = strSql;
                if (myCmd.Parameters.Count > 0)
                {
                    myCmd.Parameters.Clear();
                }
                if (arrParam != null)
                {
                    myCmd.Parameters.AddRange(arrParam);
                }

                DbDataReader rdr = myCmd.ExecuteReader((blnFirstRow ? CommandBehavior.SingleRow : CommandBehavior.SingleResult));

                return rdr;
                
             
        }
        public DbDataReader GetTableDataReader(string strTable, Dictionary<string, object> dictCond)
        {
            return GetTableDataReader(strTable, dictCond, "*");
        }
        public DbDataReader GetTableDataReader(string strTable, Dictionary<string, object> dictCond, string strFieldName)
        {


            KeyValuePair<string, IDataParameter[]> kvp = CreateCondParam(dictCond);

            string strSql = string.Format("select {0} from {1}  where {2}", strFieldName, strTable, kvp.Key);
            return GetDataReader(strSql, kvp.Value);
        }

        public DbDataReader GetDataFirstRow(string strSql)
        {
            return GetDataFirstRow( strSql,null);
        }
        public DbDataReader GetDataFirstRow(string strSql, IDataParameter[] arrParam)
        {
            return  GetDataReader(strSql, arrParam,true );
        }
        public DbDataReader GetTableDataFirstRow(string strTable, Dictionary<string, object> dictCond)
        {
            return GetTableDataFirstRow( strTable, dictCond, "*");
        }
        public DbDataReader GetTableDataFirstRow(string strTable, Dictionary<string, object> dictCond, string strFieldName)
        {
            KeyValuePair<string, IDataParameter[]> kvp = CreateCondParam(dictCond);

            string strSql = string.Format("select {0} from {1}  where {2}", strFieldName, strTable, kvp.Key);
            return GetDataReader(strSql, kvp.Value,true );
        }


        public string GetValue(string strSql)
        {
            return GetValue( strSql, null);
        }
        public string GetValue(string strSql, IDataParameter[] arrParam)
        {
            return GetValue(strSql, arrParam, string.Empty);
        }
        public  string GetValue(   string strSql, IDataParameter[] arrParam,string strOutputParaName)
        {
            return Execute(strSql, arrParam, true, strOutputParaName);


        }

        public  void ExecuteSql(    string strSql)
        {
            ExecuteSql(   strSql, null);
        }
        public  void ExecuteSql(    string strSql,IDataParameter[] arrParam)
        {
            Execute( strSql, arrParam,false);

            
        }
        private string Execute(string strSql, IDataParameter[] arrParam, bool blnNeedRt)
        {
            return Execute(strSql, arrParam, blnNeedRt, string.Empty);
        }
        private string Execute(string strSql, IDataParameter[] arrParam, bool blnNeedRt, string strOutputParaName)
        {
            LogManager.Debug(string.Format("[{0}] {1}", _server.ToString(), strSql));
            if (arrParam != null)
            {
                LogManager.Debug(GetParamLog(arrParam));
            }

            if (Connection.State == ConnectionState.Closed)
            {

                Connection.Open();
            }

            DbCommand myCmd = CreateCommand(_server.Type);
            myCmd.CommandTimeout = 60;
            if (_blnInTrans)
            {
                myCmd.Transaction = _Trans;
            }
            myCmd.CommandText = strSql;
            if (myCmd.Parameters.Count > 0)
            {
                myCmd.Parameters.Clear();
            }
            int iOutputPara=-1;
            if (arrParam != null)
            {
                if (strOutputParaName.Length > 0)
                {
                    for (int i = 0; i < arrParam.Length; i++)
                    {
                        if (arrParam[i].ParameterName.Equals((this._server.Type==ConnectionInfo.ServerType_Sql ?"@":"?")+strOutputParaName, StringComparison.InvariantCultureIgnoreCase))
                        {
                            arrParam[i].Direction = ParameterDirection.Output;
                            iOutputPara = i;
                        }
                    }
                }
                myCmd.Parameters.AddRange(arrParam);
            }
            


            if (blnNeedRt)
            {
                if (strOutputParaName.Length > 0  && iOutputPara>=0)
                {
                    
                    myCmd.ExecuteNonQuery();
                    return arrParam[iOutputPara].Value.ToString();
                }
                else
                {
                return CvtFunc.ToString(myCmd.ExecuteScalar());
                }
            }
            else
            {
                return myCmd.ExecuteNonQuery().ToString();
            }


        }

        private  string GetParamLog(IDataParameter[] arrParam)
        {
            StringBuilder str = new StringBuilder();
            foreach (IDataParameter param in arrParam)
            {
                str.AppendFormat("{0},",param.Value);
            }

            return str.ToString();
        }

      
        
        public   KeyValuePair<string, IDataParameter[]> CreateCondParam( Dictionary<string, object> dictCond)
        {
            if (_server.Type ==ConnectionInfo.ServerType_Sql)
            {
                return SqlFactory.CreateCondParam(dictCond);
            }
            else
            {
                return MySqlFactory.CreateCondParam(dictCond);
            }
        }

        public void Close()
        {
            this.Dispose();
        }
        public void Dispose()
        {
            if (_Trans != null)
            {
                if (_blnInTrans)
                {
                    _blnInTrans = false;
                    _Trans.Rollback();
                }
                _Trans.Dispose();
                _Trans = null;
            }
            if (_conn != null)
            {
                _conn.Close();
               
            }
        }
 
        private bool _blnInTrans = false;
        public void BeginTrans()
        {
            if (Connection.State == ConnectionState.Closed)
            {
                Connection.Open();
            }
             
                _Trans = Connection.BeginTransaction();
                _blnInTrans = true;
             
        }

        public void RollbackTrans()
        {
            if (_blnInTrans)
            {
                _blnInTrans = false;
                _Trans.Rollback();
            }
        }

        public void CommitTrans()
        {
            if (_blnInTrans)
            {
                _blnInTrans = false;

                _Trans.Commit();
            }
        }



        public  void CacheParameters(string cacheKey, params IDataParameter[] cmdParms)
        {
            if (_server.Type == ConnectionInfo.ServerType_Sql)
            {
                SqlFactory.CacheParameters(cacheKey,  (SqlParameter[])cmdParms);
            }
            else
            {
                MySqlFactory.CacheParameters(cacheKey, (MySqlParameter[])cmdParms);
            }
        }

        public   IDataParameter[] GetCacheParameters(string cacheKey)
        {
            if (_server.Type == ConnectionInfo.ServerType_Sql)
            {
                return SqlFactory.GetCacheParameters(cacheKey);
            }
            else
            {
                return MySqlFactory.GetCacheParameters(cacheKey);
            }
        }


    }
}